Opening Access Parameter queries
Access Parameter queries can be opened only when you open an Access database via ODBC. Make sure you have an ODBC data source set up for your Access database before attempting this procedure. See Setting up an ODBC data source.
Note: When you design a Parameter query in Access, you must provide a prompt for the query and specify a data type for the parameter. First, with your query open in Design View in Microsoft Access, enter a prompt in the Criteria cell for the field that will act as a parameter. Then, choose Parameters from the Query menu in Access, and specify a data type for the parameter you just created. Make sure the prompt appears exactly as it does in the Criteria cell. For complete instructions, refer to your Access documentation. If you do not set up your Parameter query correctly, Crystal Reports will not be able to use it.
To open an Access Parameter query
- In Crystal Reports, choose Options from the File menu.
The Options dialog box appears.
- Click the Database tab.
- Select the Stored Procedures and/or Reprompt user when connecting check box(es).
- Selecting "Stored Procedures" automatically displays any available stored procedures when you log on to an ODBC data source. Crystal Reports treats Access Parameter queries much like it treats SQL stored procedures. So, to use a Parameter query, the Stored Procedures check box must be selected.
- Selecting "Reprompt user when connecting" displays the Allow Reporting On dialog box every time you select an ODBC data source. This dialog box provides the same options as the Allow Reporting On section on the SQL tab of the Options dialog box, but provides those options for every ODBC data source you select.
- In addition, you can specify Table name LIKE and Owner LIKE options if you wish.
- "Table name LIKE" is based on the SQL LIKE clause. This option allows you to specify the kinds of table names you want to appear in the Choose SQL Table dialog box. You can use the underscore character (_) or the percent sign character (%) as wildcards with this function. The underscore character specifies any single character, while the percent sign signifies any character string. For example, DAV_ matches DAVE only, while DAV% matches DAVE and DAVID. Table name LIKE C% displays only those tables that have a table name beginning with the letter C.
- "Owner LIKE" is also based on the SQL LIKE clause. Owner LIKE allows you to select the Owner (or Creator or Alias) of the table, not the table name itself. For example Owner LIKE C% displays only those tables that have an owner beginning with the letter C.
- Click OK to exit the Options dialog box.
- Create a new report and choose ODBC as the data source for your Access database.
Tip: Choosing ODBC as your data source automatically logs you onto the server.
If you did not specify a particular Access database file with your Access ODBC data source, the Select Database dialog box appears.
Note: If your database requires a user name and password, or any other log on information, a log on dialog box appears.
- Locate and select the database that contains the Access Parameter query you want to use and then expand it.
If you selected the "Reprompt user when connecting" check box in the Options dialog box, the Allow Reporting On dialog box appears. Otherwise, skip to Step 9.
- Make sure the Procedures check box is selected and click OK when finished.
- Highlight your Parameter query and click Add.
Tip: Your Parameter query is found under the Stored Procedures branch of your ODBC data source in the Data Explorer.
The Enter Parameter Values dialog box appears.
- Highlight a parameter in the Parameter Fields list.
Set to NULL Value is selected by default. If you want to assign a value, clear the Set to NULL Value check box.
- Assign a value by typing into the Discrete Value box and then click OK.
- Repeat Steps 10 and 11 for each parameter in your Access Parameter query.
- On the Data Explorer dialog box, click Close.
You can change parameter values at any time by choosing Stored Procedure Parameters from the Database menu.
- Create your report using the fields in the Parameter query. Only the records that satisfy the parameter values you specified in the Enter Parameter Values dialog box are used in your report.
Note: You cannot use Access Action queries or Update queries in Crystal Reports. However, you can use Access Select queries and Cross-tab queries.